using System;
using System.Collections.Generic;
using System.Configuration;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Security.Authentication.ExtendedProtection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using ItProto1;
using MySqlConnector;
using RFS.BizLogic.utils;
using RFS.Domain.PaiShi.Accounting;
using RFS.Domain.PaiShi.Zuhuo;
using RFS.Domain.Sys;
using ycx;

namespace RFS.BizLogic.PaiShi.Ports.Adapters
{
    public class ZuhuaRepoMySqlAdapter : IRepoZuhuaPort
    {
        private string _connectionString = "";
        private string FuzuGonghuoma = "28282";
        private string FuzuGDk = "DK00";


        // private const string EntityTableName = "acc_tbl_AccSetEntityGraph";
        // private const string DicTableName = "acc_tbl_AccSetDic";
        private const string ZuHuaTable = "acc_tbl_组花主表";
        private const string ZuHuaDetailTable = "acc_tbl_组花明细表";


        private ILog _log;
        private IConfigT _config;

        public ZuhuaRepoMySqlAdapter(ILog log, IConfigT config)
        {
            CreategpDic();
            _log = log;
            _config = config;
            // _config = config;
            string connectionStringName = "CnnStrAccDb";
            _connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

            FuzuGonghuoma = _config.ConfigSys.FuzuGongyingShangMa;
            FuzuGDk = _config.ConfigSys.FuzuDiKuai;

            TryCreateTable();
        }


        public List<dynamic> ExecuteSqlQuery(string query)
        {
            List<dynamic> results = new List<dynamic>();


            using (MySqlConnection connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            dynamic obj = new ExpandoObject();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                var fieldName = reader.GetName(i);
                                var fieldValue = reader[i];
                                ((IDictionary<string, object>)obj).Add(fieldName, fieldValue);
                            }

                            results.Add(obj);
                        }
                    }
                }
            }

            return results;
        }


        //
        // public void SaveEntity(ZaHuaItem o)
        // {
        // }
        //
        //
        // public void DeleteEntity(AVSEntity o)
        // {
        //     var dic = o.ToAccItemAndOrderBasedDicItem();
        //
        //     using (var connection = new MySqlConnection(_connectionString))
        //     {
        //         connection.Open();
        //
        //         using (var tran = connection.BeginTransaction())
        //         {
        //             #region de entity
        //
        //             var sql1 = $"DELETE FROM {EntityTableName} WHERE ID = {o.Id}";
        //             using (var command = new MySqlCommand(sql1, connection, tran))
        //             {
        //                 command.ExecuteNonQuery();
        //             }
        //
        //             #endregion
        //
        //             #region de dic for search
        //
        //             if (dic.Count() > 0)
        //             {
        //                 var sql4 =
        //                     $"delete from  {DicTableName} where AccItemId in ({dic.Select(x => x.AccItemId).JoinToString()})";
        //                 using (var command = new MySqlCommand(sql4, connection, tran))
        //                 {
        //                     command.ExecuteNonQuery();
        //                 }
        //             }
        //
        //             #endregion
        //
        //             tran.Commit();
        //         }
        //
        //         connection.Close();
        //     }
        // }
        //
        // public AVSEntity GetById(ulong id)
        // {
        //     var sql = $"select id,entityJson FROM {EntityTableName} WHERE ID = {id}";
        //     var obj = ExecuteSqlQuery(sql);
        //     if (obj.Count == 0) throw new Exception($"找不到记录【{id}】。");
        //
        //     var json = $"{obj[0].entityJson}";
        //     var en = json.ToObject<AVSEntity>();
        //     return en;
        // }


        /// <summary>
        /// 移除空字节
        /// </summary>
        /// <param name="bytes"></param>
        /// <returns></returns>
        public string ByteTostring(byte[] bytes)
        {
            List<byte> dataList = bytes.ToList();
            dataList.RemoveAll(b => b == 0);
            byte[] cleanedData = dataList.ToArray();
            string result = Encoding.UTF8.GetString(cleanedData);
            return result;
        }


        public IEnumerable<ZuhuaRecord> GetZuhuaRecordsByDay(DateTime day)
        {
            var date = day;
            //date(z.扫码时间)='2025-1-14'
            var sql =
                $@"select    Id, ZuHuoType, ZHTime, 发货单码 , 等级 , 批次,复核人,录单人 ,拍市品种码,拍市品种名,EntityJson  FROM {ZuHuaTable} z where date(z.ZHTime)='{date:yyyy-MM-dd}' ORDER BY  Id desc  ";
            var dobj = ExecuteSqlQuery(sql);
            var res = dobj.Select(x =>
            {
                var re = $"{x.EntityJson}".ToObject<ZuhuaRecord>();

                re.FaHuoZiLingItems = GetZuhuaRecordsItemsById(re.Id).ToList();
                return re;
            }).ToList();
            return res;
        }

        public IEnumerable<ZuhuaZiLingItem> GetZuhuaRecordsItemsById(ulong id)
        {
            var date = id;
            var sql =
                $@"select   Id, ParentId, ZaHuaItemId  FROM {ZuHuaDetailTable} z where  ParentId='{date}' ORDER BY  Id    ";
            var dobj = ExecuteSqlQuery(sql);
            var res = dobj.Select(x =>
            {
                var re = new ZuhuaZiLingItem()
                {
                    Id = (ulong)x.Id,
                    ParentId = x.ParentId,
                    ZaHuaItemId = x.ZaHuaItemId,
                };
                return re;
            }).ToList();

            return res;
        }


        public IList<ProductTypeItem> ProdTypeItems { get; set; } = new List<ProductTypeItem>();

        string GetGHMa(ZuhuaRecord zu)
        {
            // idx2+days3+q2(20based)+len2+suplier6+paishi(02127)
            // -DATE(2024,12,1)

            DateTime bd = _config.ConfigSys.KapiCodingBaseDate;
            // var bd = ;

            var totaldays = ((int)(zu.ZHTime - bd).TotalDays) +1 ;

            var idx = zu.长度基批次;
            var kipaCode = zu.拍市品种码;
            var re =
                $"{idx:00}{totaldays:000}{zu.枝数 / _config.ConfigSys.QuantiyPerZaInPingYuan:00}{int.Parse(zu.ProdDemo.长度):00}{int.Parse(zu.供货商码):000000}{int.Parse(kipaCode):00000}";
            "01048205503033502127 -20位".Required(re.Length == 20);
            //01047055003034402622
            return re;
        }


        public List<Zuhuaer> BuildGroupZuhuo(IEnumerable<ZaHuaItem> zh, ZuHuoType bizType = ZuHuoType.首次组花)
        {
            // 100-300 -- 地品长


            var zrange = _config.ConfigSys.ZuhuoQuantityRangeInZa;

            var gp = new Dictionary<string, Zuhuaer>();


            var gs = bizType == ZuHuoType.首次组花
                ? zh.GroupBy(x => $"{x.供货商码}_{x.GroupKey}")
                : (bizType == ZuHuoType.二次组花 ? zh.GroupBy(x => $"{x.品种}_{x.长度}") : zh.GroupBy(x => $"{x.长度}"));

            foreach (var kv in gs)
            {
                var idx = 1;
                var gz = new Zuhuaer(zrange.min, zrange.max);
                var k = $"{kv.Key}_{idx}";
                gp[k] = gz;
                foreach (var za in kv)
                {
                    if (!gz.IsFull)
                    {
                        gz.AddZaHuaItem(za);
                    }
                    else
                    {
                        //is full create new one
                        idx++;
                        gz = new Zuhuaer(zrange.min, zrange.max);
                        k = $"{kv.Key}_{idx}";
                        gp[k] = gz;
                        gz.AddZaHuaItem(za);
                        gz.GroupBasedIndex = idx - 1;
                    }
                }
            }

            return gp.Values.ToList();
        }


        public (List<ZuhuaRecord> succ, List<ZaHuaItem> failed) CreateZuhuoPlans(IEnumerable<Zuhuaer> gs,
            ZuHuoType zuhuoType, bool s)
        {
            var res = CreateZuhuoPlansInner(gs, zuhuoType);

            if (s)
            {
                SaveAsync(res.succ.ToList());
            }


            return res;
        }


        (List<ZuhuaRecord> succ, List<ZaHuaItem> failed) CreateZuhuoPlansInner(IEnumerable<Zuhuaer> gs,
            ZuHuoType zuhuoType)
        {
            //1 do first grouping. all g 
            var goodGroups = gs.Where(x => x.IsEnouph);
            var wd = gs.ElementAt(0).ZahuoItems.FirstOrDefault().供货日期;


            //2 only evenitems that not full 
            var leftoverZasNotfull = gs
                .Where(x => !x.IsEnouph)
                .SelectMany(x => x.EvenItems);

            var leftoverZasNotEvent = gs
                .Where(x => !x.IsEven)
                .Select(x => x.OddItem);

            var ls = new List<ZaHuaItem>();
            ls.AddRange(leftoverZasNotfull);
            ls.AddRange(leftoverZasNotEvent);
            // var goup2ndTime=Zuhuaer.BuildGroupZuhuo(ls,supplierSplitted: false);

            var succeeded = goodGroups.Sum(x => x.EvenItems.Count());
            var failed = ls.Count();

            var totalToGrouped = gs.Sum(x => x.ZahuoItems.Count());
            $"待组数【{totalToGrouped}】=已组数【{succeeded}】 + 余花数【{failed}】 不成立".Required(totalToGrouped ==
                succeeded + failed);

            var lszh = new List<ZuhuaRecord>();
            //create the 1st grouping plan
            foreach (var g in goodGroups)
            {
                var zas = g.EvenItems;
                var zuhuaRecord = new ZuhuaRecord()
                {
                    Id = 1.NewSnowflakeId(),

                    CreateTime = DateTime.Now,
                    ZuHuoType = zuhuoType,
                    FaHuoZiLingItems = new List<ZuhuaZiLingItem>(),
                };
                foreach (var za in zas)
                {
                    var zuhuaZiLingItem = new ZuhuaZiLingItem()
                    {
                        Id = 1.NewSnowflakeId(),
                        ZaHuaItem = za,
                        ParentId = zuhuaRecord.Id,
                    };
                    zuhuaRecord.FaHuoZiLingItems.Add(zuhuaZiLingItem);
                }

                var n = DateTime.Now;
                zuhuaRecord.ZHTime = wd.Date.AddHours(n.Hour).AddMinutes(n.Minute).AddSeconds(n.Second);

                FillPaishiInfo(zuhuaRecord);


                lszh.Add(zuhuaRecord);
            }


            return (lszh, ls);
        }

        #region deng ji

        private readonly string _jsonDengjiFile = "Dengji.json";

        private string JsonDengjiJson
        {
            get
            {
                if (File.Exists(_jsonDengjiFile))
                    return File.ReadAllText("Dengji.json");
                else
                {
                    return "";
                }
            }
        }

        IEnumerable<KipaDengJi> _dengji;

        public IEnumerable<KipaDengJi> GetDengji()
        {
            if (_dengji == null)
            {
                _dengji = JsonDengjiJson.ToObject<List<KipaDengJi>>();
            }

            return _dengji;
        }

        string ZuhuaRecordToDengJi(ZuhuaRecord r)
        {
            var d = GetDengji().FirstOrDefault(x => x.拍码长 == $"0{r.拍市品种码}-{r.ProdDemo.长度}");
            if (d == null) return "";
            return d.等级;
        }

        #endregion

        Dictionary<string, int> _dkBasedidx = new Dictionary<string, int>();


        // private string PyCodeToKIPACode(ZaHuaItem p)
        // {
        //     if (p==null) return "" ;
        //     var it = ProdTypeItems.FirstOrDefault(x => int.Parse(x.品品编码) == int.Parse(p.品种));
        //     if (it == default) throw new Exception($"[{p.ToJson()}] 品种码不对");
        //     return it.拍品编码;
        // }

        private string zu3pzCode = "2999";
        private string zu3Pz = "混单玫瑰";
        
        
        private void FillPaishiInfo(ZuhuaRecord r )
        {
            var it = ProdTypeItems.FirstOrDefault(x => int.Parse(x.品品编码) == int.Parse(r.ProdDemo.品种));
            if (it == default) throw new Exception($"[{r.ProdDemo}] 品种码不对");
            r.拍市品种码 = it.拍品编码;
            r.拍市品种名 = it.拍品名称;
            r.长度 = r.ProdDemo.长度 ;
            
            
            if (r.ZuHuoType != ZuHuoType.首次组花)
            {
                r.供货商码 = FuzuGonghuoma;
                r.地块 = FuzuGDk;
                
                if (r.ZuHuoType == ZuHuoType.三次组花 )
                {
                    r.拍市品种码 = zu3pzCode;
                    r.拍市品种名 = zu3Pz ;
                }
            }
            else
            {
                // first grouing. 
                r.供货商码 = r.ProdDemo.供货商码;
                r.地块 = r.ProdDemo.地块;
                
            }


            #region 地块+日 基批次

            r.批次 = GetNewIndexOfDDB(r)  ;

            #endregion


            #region 【日+地块+长度】 基批次

            r.长度基批次 = GetNewIdxOfDDL( r)  ;

            #endregion


            r.等级 = ZuhuaRecordToDengJi(r);
            r.录单人 = _config.ConfigSys.ZuhuoEditor;
            r.复核人 = _config.ConfigSys.ZuhuoDoubleChecker;
            r.规格 = $"{_config.ConfigSys.QunatityKipaPerZa}";

            r.发货单码 = GetGHMa(r);
        }

        #region 【日+地块+长度】 基批次

        
        Dictionary<string, int> ddlBasedDic   ;

        Dictionary<string, int> GetLastIndexOfDDLBase(   )
        {
            var src = _zuhuoManager.ZuHuoItems.ToList();
            Dictionary<string, int> ddlBasedDic = new Dictionary<string, int>();

            var re = 0;
            foreach (var r in src)
            {
                var isFuzu = r.ZuHuoType == ZuHuoType.首次组花 ? "1" : "F";

                var DDLBasedKey = $"{isFuzu}_{r.ProdDemo.GetDays(r.ProdDemo.供货日期)}{r.地块}{r.ProdDemo.长度}";
                if (ddlBasedDic.ContainsKey(DDLBasedKey)) ddlBasedDic[DDLBasedKey]++;
                else ddlBasedDic[DDLBasedKey] = 1;
               
            }


            return  ddlBasedDic ;
        }
        int GetNewIdxOfDDL(  ZuhuaRecord r)
        {
             

            if (ddlBasedDic == null || _zuhuoManager.ZuHuoItems.Count()<1  ) 
                ddlBasedDic = GetLastIndexOfDDLBase();
            

            
                var isFuzu = r.ZuHuoType == ZuHuoType.首次组花 ? "1" : "F";
                var DDLBasedKey = $"{isFuzu}_{r.ProdDemo.GetDays(r.ProdDemo.供货日期)}{r.地块}{r.ProdDemo.长度}";
                
                if (ddlBasedDic.ContainsKey(DDLBasedKey)) 
                    ddlBasedDic[DDLBasedKey]++;
                else ddlBasedDic[DDLBasedKey] = 1;
          

            return ddlBasedDic[DDLBasedKey] ;
        }

        #endregion

        #region 地块+日

        private IZuhuoManager _zuhuoManager;

        public void RegsiterManager(IZuhuoManager m)
        {
            _zuhuoManager = m;
        }

        public void FillKapiInfo(ZuhuaRecord z)
        {
            try
            {
                FillPaishiInfo(z );

            }
            catch (Exception e)
            {
                throw new Exception($"输入数据有问题:{e.Message}", e);

            }
        }


        /// <summary>
        /// get the conter from data
        /// </summary>
        /// <returns></returns>
        Dictionary<string, int> GetDataBase()
        {
            
            var src = _zuhuoManager.ZuHuoItems.ToList();
            _ddBatchBasedDic = new Dictionary<string, int>();
            foreach (var rt in src)
            {
                var isFuzu = rt.ZuHuoType == ZuHuoType.首次组花 ? "1" : "F";
                var k = $"{isFuzu}_{rt.ProdDemo.GetDays(rt.ProdDemo.供货日期)}{rt.地块}";

                if (_ddBatchBasedDic.ContainsKey(k))
                    _ddBatchBasedDic[k]++;
                else
                    _ddBatchBasedDic[k] = 1;
            }

            return _ddBatchBasedDic;
            
        }
        
        
        Dictionary<string, int> _ddBatchBasedDic;

        int GetNewIndexOfDDB(ZuhuaRecord r)
        {
             

            var isFuzu = r.ZuHuoType == ZuHuoType.首次组花 ? "1" : "F";
            var DDLBasedKey = $"{isFuzu}_{r.ProdDemo.GetDays(r.ProdDemo.供货日期)}{r.地块}";

            if (_ddBatchBasedDic == null || _zuhuoManager.ZuHuoItems.Count()<1 ) 
                _ddBatchBasedDic = GetDataBase();

            if (_ddBatchBasedDic.ContainsKey(DDLBasedKey))
            {
                _ddBatchBasedDic[DDLBasedKey]++;
            }
            else
            {
                _ddBatchBasedDic[DDLBasedKey] = 1;
            }
 
            return _ddBatchBasedDic[DDLBasedKey];
        }

        #endregion


        void CreategpDic()
        {
            var ls = _prodDic.ToObject<List<ProductTypeItem>>();
            ProdTypeItems = ls;
        }


        // zahua code to infos
        //007.04.55.10-T25-C01-DK22_02:P1F_0018
        // PZcode.DENGJI.LEN.GG-TIANGANG-DK-
        // public void SaveTheAvsListItem(IDayBasedWorkFlowNodeAccountingVoucherSetWrapper o)
        // {
        //     var entity = o.ToListItem();
        //
        //     using (var connection = new MySqlConnection(_connectionString))
        //     {
        //         connection.Open();
        //
        //         using (var tran = connection.BeginTransaction())
        //         {
        //             var sql1 = $"DELETE FROM {ZaHuaTable} WHERE ID = {o.Id}";
        //             using (var command = new MySqlCommand(sql1, connection, tran))
        //             {
        //                 command.ExecuteNonQuery();
        //             }
        //
        //
        //             string sql3 = $@"
        //             INSERT INTO {ZaHuaTable} (id,EndAt,BeginAt,BalanceAmountQ,BalanceAmountC,DebitAmountQ,DebitAmountC,CreditAmoutQ,CreditAmoutC )
        //                             VALUES (@Id, @EndAt, @BeginAt, @BalanceAmountQ, @BalanceAmountC, @DebitAmountQ, @DebitAmountC, @CreditAmoutQ, @CreditAmoutC );
        //         ";
        //
        //             using (var command = new MySqlCommand(sql3, connection, tran))
        //             {
        //                 command.Parameters.AddWithValue("@Id", entity.Id);
        //                 command.Parameters.AddWithValue("@EndAt", entity.AccPeriod.EndDate);
        //                 command.Parameters.AddWithValue("@BeginAt", entity.AccPeriod.BeginDate);
        //                 command.Parameters.AddWithValue("@BalanceAmountQ", entity.TotalBalance.Quantity);
        //                 command.Parameters.AddWithValue("@BalanceAmountC", entity.TotalBalance.AmountCash);
        //                 command.Parameters.AddWithValue("@DebitAmountQ", entity.DebitAmount.Quantity);
        //                 command.Parameters.AddWithValue("@DebitAmountC", entity.DebitAmount.AmountCash);
        //                 command.Parameters.AddWithValue("@CreditAmoutQ", entity.CreditAmout.Quantity);
        //                 command.Parameters.AddWithValue("@CreditAmoutC", entity.CreditAmout.AmountCash);
        //                 // command.Parameters.AddWithValue("@IsChecked", entity.IsChecked);
        //                 command.ExecuteNonQuery();
        //             }
        //
        //
        //             tran.Commit();
        //             connection.Close();
        //         }
        //     }
        // }


        private string _prodDic = $@"
[
  {{
    ""拍品编码"": ""2999"",
    ""品品编码"": ""2999"",
    ""拍品名称"": ""混单玫瑰"",
    ""品元名称"": ""混单玫瑰"",
    ""品种简称"": ""混单玫瑰""
  }},  
{{
    ""拍品编码"": ""2619"",
    ""品品编码"": ""001"",
    ""拍品名称"": ""夏日阳光"",
    ""品元名称"": ""夏日阳光"",
    ""品种简称"": ""夏日""
  }},
  {{
    ""拍品编码"": ""2543"",
    ""品品编码"": ""002"",
    ""拍品名称"": ""甜蜜雪山"",
    ""品元名称"": ""娇兰雪山"",
    ""品种简称"": ""娇兰""
  }},
  {{
    ""拍品编码"": ""2181"",
    ""品品编码"": ""003"",
    ""拍品名称"": ""糖果雪山"",
    ""品元名称"": ""糖果雪山"",
    ""品种简称"": ""糖果""
  }},
  {{
    ""拍品编码"": ""2374"",
    ""品品编码"": ""004"",
    ""拍品名称"": ""蜜桃雪山"",
    ""品元名称"": ""蜜桃雪山"",
    ""品种简称"": ""蜜桃""
  }},
  {{
    ""拍品编码"": ""2027"",
    ""品品编码"": ""005"",
    ""拍品名称"": ""雪山"",
    ""品元名称"": ""经典雪山"",
    ""品种简称"": ""经典""
  }},
  {{
    ""拍品编码"": ""2472"",
    ""品品编码"": ""006"",
    ""拍品名称"": ""紫霞仙子"",
    ""品元名称"": ""夜莺"",
    ""品种简称"": ""夜莺""
  }},
  {{
    ""拍品编码"": ""2488"",
    ""品品编码"": ""007"",
    ""拍品名称"": ""艾莎"",
    ""品元名称"": ""爱莎"",
    ""品种简称"": ""爱莎""
  }},
  {{
    ""拍品编码"": ""2923"",
    ""品品编码"": ""008"",
    ""拍品名称"": ""珍爱"",
    ""品元名称"": ""珍爱"",
    ""品种简称"": ""珍爱""
  }},
  {{
    ""拍品编码"": ""2127"",
    ""品品编码"": ""009"",
    ""拍品名称"": ""粉红雪山"",
    ""品元名称"": ""粉红雪山"",
    ""品种简称"": ""粉红""
  }},
  {{
    ""拍品编码"": ""2622"",
    ""品品编码"": ""014"",
    ""拍品名称"": ""洛神"",
    ""品元名称"": ""洛神"",
    ""品种简称"": ""洛神""
  }}
]";


        // private const string EntityTableName = "acc_EntityTableName";
        // private const string EntityDetailItemsTableName = "acc_EntityTableNameit";


        public void ReCreateTables(string pwd)
        {
            if (pwd == "a")
            {
                ExecuteSqlQuery($"DROP TABLE {ZuHuaTable};");
                ExecuteSqlQuery($"DROP TABLE {ZuHuaDetailTable};");
            }


            TryCreateTable();
        }

        public void Dels(IEnumerable<ZuhuaRecord> os, bool nolog=false )
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                using (var tran = connection.BeginTransaction())
                {
                    try
                    {
                        foreach (var o in os)
                        {
                            #region del

                            var sql1 = $"DELETE FROM {ZuHuaTable} WHERE ID = {o.Id}";
                            var sql4d = $"delete from  {ZuHuaDetailTable} where ParentId={o.Id} ";


                            using (var command = new MySqlCommand(sql1, connection, tran))
                            {
                                command.ExecuteNonQuery();
                            }


                            using (var command = new MySqlCommand(sql4d, connection, tran))
                            {
                                command.ExecuteNonQuery();
                            }

                            #endregion
                        }

                       if (!nolog) _log.Info($"[{os.Count()}] 删除成功");

                        tran.Commit();
                        connection.Close();
                    }
                    catch (Exception e)
                    {
                        _log.Err(new Exception($"[{os.Count()}]项 删除失败:[{e.Message}]", e));
                        tran.Rollback();
                    }
                }
            }
        }

        private void TryCreateTable()
        {
            var sql1 = $@"
   CREATE TABLE IF NOT EXISTS `{ZuHuaTable}` (
    `Id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ZuHuoType` INT NOT NULL,  
    `ZHTime` DATETIME NOT NULL,
    `CreateTime` DATETIME NOT NULL,
    `发货单码` VARCHAR(255) NOT NULL,
    `拍市品种码` VARCHAR(255) NOT NULL,
    `拍市品种名` VARCHAR(255) NOT NULL,
    `等级` VARCHAR(255) NOT NULL,
    `批次` INT NOT NULL,
    `复核人` VARCHAR(255) NOT NULL,
    `录单人` VARCHAR(255) NOT NULL,
    `EntityJson` LONGTEXT  NOT NULL,
     
    PRIMARY KEY (`Id`)
)   DEFAULT CHARSET=utf8mb4;
";


            var sql2 = $@"
   CREATE TABLE IF NOT EXISTS `{ZuHuaDetailTable}` (
    `Id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ParentId` BIGINT UNSIGNED NOT NULL  ,
    `ZaHuaItemId` BIGINT UNSIGNED NOT NULL  ,
    
     
    PRIMARY KEY (`Id`)
)   DEFAULT CHARSET=utf8mb4;
";

            ExecuteSqlQuery(sql1);
            ExecuteSqlQuery(sql2);
        }


        private static readonly SemaphoreSlim _semaphore = new SemaphoreSlim(1, 1);
        private static readonly SemaphoreSlim _semaphoreEns = new SemaphoreSlim(1, 1);

        public async Task SaveAsync(ZuhuaRecord o)
        {
            await Task.Run(async () =>
            {
                await _semaphore.WaitAsync();
                try
                {
                    SaveInner(o);
                }
                finally
                {
                    _semaphore.Release();
                }
            });
        }

        public async Task SaveAsync(IEnumerable<ZuhuaRecord> os)
        {

            _log.Info($"已启动保存任务：【{os.Count()}】项。需要20秒。");
            await Task.Run(async () =>
            {

                if ( os.Count()<1) return;
                Dels(os,true);
                var sv = new ObjectSaver<ZuhuaRecord>(_connectionString, os, 5, _log);
                await sv.SaveObjectsAsync((c, t, o) =>
                    SavetoDb2Async(o, c, t));

            });
            
            
        }

        


        void SavetoDb2Async(ZuhuaRecord o, MySqlConnection connection, MySqlTransaction tran)
        {
            // await Task.Run(() =>
            // {

            #region saving entity
 

            string sql = $@"
                        INSERT INTO {ZuHuaTable} (Id,ZuHuoType, ZHTime, CreateTime, `发货单码`,`拍市品种码`,`拍市品种名`,`等级`,`批次`,`复核人`,`录单人` ,`EntityJson` )
                        VALUES (@Id, @ZuHuoType, @ZHTime,  @CreateTime, @发货单码, @拍市品种码,@拍市品种名, @等级, @批次, @复核人, @录单人 , @EntityJson );
                    ";
            using (var command = new MySqlCommand(sql, connection, tran))
            {
                command.Parameters.AddWithValue("@id", o.Id);
                command.Parameters.AddWithValue("@ZuHuoType", o.ZuHuoType);
                command.Parameters.AddWithValue("@ZHTime", o.ZHTime);
                command.Parameters.AddWithValue("@CreateTime", o.CreateTime);
                command.Parameters.AddWithValue("@发货单码", o.发货单码);
                command.Parameters.AddWithValue("@拍市品种码", o.拍市品种码);
                command.Parameters.AddWithValue("@拍市品种名", o.拍市品种名);

                command.Parameters.AddWithValue("@等级", o.等级);
                command.Parameters.AddWithValue("@批次", o.批次);
                command.Parameters.AddWithValue("@复核人", o.复核人);
                command.Parameters.AddWithValue("@录单人", o.录单人);
                command.Parameters.AddWithValue("@EntityJson", o.ToJson());
                command.ExecuteNonQuery();
            }

            #endregion

            #region saving dic for search

            var dic = o.FaHuoZiLingItems;
            if (dic.Count() > 0)
            {
                foreach (var entity in dic)
                {
                    string sql3 = $@"
                                    INSERT INTO {ZuHuaDetailTable} (Id,ParentId,ZaHuaItemId )
                                                    VALUES          (@Id, @ParentId, @ZaHuaItemId );
                                ";

                    using (var command = new MySqlCommand(sql3, connection, tran))
                    {
                        command.Parameters.AddWithValue("@Id", entity.Id);
                        command.Parameters.AddWithValue("@ParentId", entity.ParentId);
                        command.Parameters.AddWithValue("@ZaHuaItemId", entity.ZaHuaItemId);

                        command.ExecuteNonQuery();
                    }
                }
            }

            #endregion

            _log.Info($"[{o.发货单码}] 组货保存成功");
            // });
        }

        void SaveInner(ZuhuaRecord o)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                using (var tran = connection.BeginTransaction())
                {
                    try
                    {
                        SavetoDbAsync(o, connection, tran);

                        tran.Commit();
                        connection.Close();
                    }
                    catch (Exception e)
                    {
                        _log.Err(new Exception($"[{o.发货单码}] 保存失败:[{e.Message}]", e));
                        tran.Rollback();
                    }
                }
            }
        }

        private async Task SavetoDbAsync(ZuhuaRecord o, MySqlConnection connection, MySqlTransaction tran)
        {
            await Task.Run(async () =>
            {
                await _semaphoreEns.WaitAsync();
                try
                {
                    #region saving entity

                    #region del

                    var sql1 = $"DELETE FROM {ZuHuaTable} WHERE ID = {o.Id}";
                    var sql4d = $"delete from  {ZuHuaDetailTable} where ParentId={o.Id} ";


                    using (var command = new MySqlCommand(sql1, connection, tran))
                    {
                        command.ExecuteNonQuery();
                    }


                    using (var command = new MySqlCommand(sql4d, connection, tran))
                    {
                        command.ExecuteNonQuery();
                    }

                    #endregion


                    string sql = $@"
                        INSERT INTO {ZuHuaTable} (Id,ZuHuoType, ZHTime, CreateTime, `发货单码`,`拍市品种码`,`拍市品种名`,`等级`,`批次`,`复核人`,`录单人` ,`EntityJson` )
                        VALUES (@Id, @ZuHuoType, @ZHTime,  @CreateTime, @发货单码, @拍市品种码,@拍市品种名, @等级, @批次, @复核人, @录单人 , @EntityJson );
                    ";
                    using (var command = new MySqlCommand(sql, connection, tran))
                    {
                        command.Parameters.AddWithValue("@id", o.Id);
                        command.Parameters.AddWithValue("@ZuHuoType", o.ZuHuoType);
                        command.Parameters.AddWithValue("@ZHTime", o.ZHTime);
                        command.Parameters.AddWithValue("@CreateTime", o.CreateTime);
                        command.Parameters.AddWithValue("@发货单码", o.发货单码);
                        command.Parameters.AddWithValue("@拍市品种码", o.拍市品种码);
                        command.Parameters.AddWithValue("@拍市品种名", o.拍市品种名);

                        command.Parameters.AddWithValue("@等级", o.等级);
                        command.Parameters.AddWithValue("@批次", o.批次);
                        command.Parameters.AddWithValue("@复核人", o.复核人);
                        command.Parameters.AddWithValue("@录单人", o.录单人);
                        command.Parameters.AddWithValue("@EntityJson", o.ToJson());
                        command.ExecuteNonQuery();
                    }

                    #endregion

                    #region saving dic for search

                    var dic = o.FaHuoZiLingItems;
                    if (dic.Count() > 0)
                    {
                        foreach (var entity in dic)
                        {
                            string sql3 = $@"
                                    INSERT INTO {ZuHuaDetailTable} (Id,ParentId,ZaHuaItemId )
                                                    VALUES          (@Id, @ParentId, @ZaHuaItemId );
                                ";

                            using (var command = new MySqlCommand(sql3, connection, tran))
                            {
                                command.Parameters.AddWithValue("@Id", entity.Id);
                                command.Parameters.AddWithValue("@ParentId", entity.ParentId);
                                command.Parameters.AddWithValue("@ZaHuaItemId", entity.ZaHuaItemId);

                                command.ExecuteNonQuery();
                            }
                        }
                    }

                    #endregion

                    _log.Info($"[{o.发货单码}] 组货保存成功");
                }
                finally
                {
                    _semaphoreEns.Release();
                }
            });
        }

        public void Del(ulong id)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                using (var tran = connection.BeginTransaction())
                {
                    #region del

                    var sql1 = $"DELETE FROM {ZuHuaTable} WHERE ID = {id}";
                    var sql4d = $"delete from  {ZuHuaDetailTable} where ParentId={id} ";


                    using (var command = new MySqlCommand(sql1, connection, tran))
                    {
                        command.ExecuteNonQuery();
                    }


                    using (var command = new MySqlCommand(sql4d, connection, tran))
                    {
                        command.ExecuteNonQuery();
                    }

                    #endregion

                    tran.Commit();
                    connection.Close();
                }
            }
        }
    }
}